package com.b2c.repository;

import com.b2c.entity.result.PagingResponse;
import com.b2c.common.utils.MD5Utils;
import com.b2c.common.utils.RandomUtils;
import com.b2c.entity.ManageGroupEntity;
import com.b2c.entity.WmsManagePermissionEntity;
import com.b2c.entity.vo.WmsMangeUserPermissionKeyVo;
import com.b2c.entity.ManageUserEntity;
import com.b2c.entity.vo.WmsManageUserMenuVo;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * @Description: pbd add 2019/9/28 9:54
 */
@Repository
public class ErpUserRepository {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    private static Logger log = LoggerFactory.getLogger(ErpUserRepository.class);

    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    /**
     * 查询管理员分组
     *
     * @param pageIndex
     * @param pageSize
     * @param name
     * @param mobile
     * @return
     */
    public PagingResponse<ManageUserEntity> getWmsManageUsers(int pageIndex, int pageSize, String name, String mobile) {
        List<Object> params = new ArrayList<>();
        StringBuilder sb = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS a.id,a.user_name,a.user_pwd,a.user_salf,a.true_name,a.mobile,a.group_id,a.status");
        sb.append(",FROM_UNIXTIME(a.add_time, '%Y-%m-%d %H:%i:%S') add_time,b.group_name,a.systemId ");
        sb.append(" from ").append(Tables.SysManageUser).append(" a ");
        sb.append(" LEFT JOIN ").append(Tables.SysManageGroup).append(" b ON a.group_id=b.id where 1=1 ");
        if (!StringUtils.isEmpty(name)) {
            sb.append(" AND a.true_name like ? ");
            params.add("%" + name + "%");
        }
        if (!StringUtils.isEmpty(mobile)) {
            sb.append(" AND a.mobile like ? ");
            params.add("%" + mobile + "%");
        }
        sb.append(" ORDER BY systemId desc,a.id ASC LIMIT ?,?");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);
        List<ManageUserEntity> userList = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(ManageUserEntity.class), params.toArray(new Object[params.size()]));
        return new PagingResponse<>(pageIndex, pageSize, getTotalSize(), userList);
    }

    /**
     * 查询管理员信息
     *
     * @param userId
     * @return
     */
    public ManageUserEntity getWmsManageUser(Integer userId) {
        StringBuilder sb = new StringBuilder("SELECT SQL_CALC_FOUND_ROWS a.id,a.user_name,a.user_pwd,a.user_salf,a.true_name,a.mobile,a.group_id,a.status,FROM_UNIXTIME(a.add_time, '%Y-%m-%d %H:%i:%S') add_time,b.group_name from " + Tables.SysManageUser + " a LEFT JOIN " + Tables.SysManageGroup + " b ON a.group_id=b.id where a.id=? ");
        try {
            return jdbcTemplate.queryForObject(sb.toString(), new BeanPropertyRowMapper<>(ManageUserEntity.class), userId);
        } catch (Exception e) {
            return null;
        }

    }

    /**
     * 添加管理员
     *
     * @param name    姓名
     * @param mobile  手机号
     * @param pwd     密码
     * @param groupId 分组id
     * @param state   状态
     */
    public void executeWmsManageUser(Integer userId,String userName, String name, String mobile, String pwd, String groupId, String state) {
        String oldpwd = "";
        if (userId > 0)
            oldpwd = jdbcTemplate.queryForObject("SELECT user_pwd from "+Tables.SysManageUser+" where id=?", String.class, userId);
        String pwd_rand = RandomUtils.randomString(8);
        String password = !StringUtils.isEmpty(pwd) ? MD5Utils.MD5Encode(MD5Utils.MD5Encode(pwd) + pwd_rand) : oldpwd;
        if (userId > 0 && !StringUtils.isEmpty(pwd)) {
            jdbcTemplate.update("UPDATE "+Tables.SysManageUser+" SET true_name=?,user_pwd=?,user_salf=?,mobile=?,group_id=?,status=?,user_name=? WHERE id=?", name, password, pwd_rand, mobile, groupId, state,userName, userId);
        } else if (userId <= 0) {
            //新增
            String wmsManageUserSQL = "insert into "+Tables.SysManageUser+" set true_name =?,user_pwd=?,user_salf=?,mobile = ?,group_id=?,status=?,user_name=?,add_time=unix_timestamp(now())";
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(connection -> {
                PreparedStatement ps = connection.prepareStatement(wmsManageUserSQL, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, name);
                ps.setString(2, password);
                ps.setString(3, pwd_rand);
                ps.setString(4, mobile);
                ps.setString(5, groupId);
                ps.setString(6, state);
                ps.setString(7, userName);
                return ps;
            }, keyHolder);
            initUserPermissionMenu(keyHolder.getKey().intValue());
        } else {
            jdbcTemplate.update("UPDATE "+Tables.SysManageUser+" SET true_name=?,mobile=?,group_id=?,status=?,user_name=? WHERE id=?", name, mobile, groupId, state,userName, userId);
        }
    }

    /**
     * 删除管理员
     *
     * @param userId
     */
    public void delWmsManageUser(Integer userId) {
        jdbcTemplate.update("DELETE FROM  "+Tables.SysManageUser+" WHERE id=?", userId);
        jdbcTemplate.update("delete from "+Tables.SysManageUserPermission+" where user_id=?", userId);
    }

    /**
     * 检查用户是否存在
     *
     * @param mobile
     * @return
     */
    public int checkManageUserByMobile(Integer userId, String mobile) {
        if (userId > 0)
            return jdbcTemplate.queryForObject("SELECT IFNULL(COUNT(id),0)  from "+Tables.SysManageUser+" where (id>? OR id<?) and mobile=?", Integer.class, userId, userId, mobile);
        return jdbcTemplate.queryForObject("SELECT IFNULL(COUNT(id),0)  from "+Tables.SysManageUser+" where mobile=?", Integer.class, mobile);
    }

    /**
     * 查询所有管理员分组
     *
     * @return
     */
    public List<ManageGroupEntity> getManageGroups() {
        return jdbcTemplate.query("SELECT  * FROM "+Tables.SysManageGroup+" where 1=1 ", new BeanPropertyRowMapper<>(ManageGroupEntity.class));
    }

    /**
     * 根据用户名查询用户
     *
     * @param userName
     * @return
     */
    public ManageUserEntity getUserByUserName(String userName) {
        log.info("查询用户名" + userName);
        String sql = "SELECT * FROM "+Tables.SysManageUser+" WHERE  (user_name=? or mobile= ?) LIMIT 1";
        log.info("查询用户名" + sql);
        List<ManageUserEntity> users = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ManageUserEntity.class), userName, userName);
        if (users == null || users.size() == 0) return null;
        else return users.get(0);
    }

    /**
     * 查询用户已授权菜单,存在返回true
     *
     * @param userId
     * @return
     */
    public boolean checkUserPermissionMenu(Integer userId, String permissionKey) {
        String permissionkeySQL = "select wmp.permission_key from wms_manage_user_permission wmup LEFT JOIN wms_manage_permission wmp ON wmup.permission_id=wmp.id where wmup.user_id=? and wmup.status=0 ";
        var list = jdbcTemplate.query(permissionkeySQL, new BeanPropertyRowMapper<>(WmsMangeUserPermissionKeyVo.class), userId);
        return list.contains(permissionKey);
    }

    /**
     * 查询用户已授权菜单访问地址,已授权返回true
     *
     * @param userId
     * @return
     */
    public boolean checkUserPermissionMenuByUrl(Integer userId, String url) {
        /**1.超级管理员全部授权**/
        Integer groupId = jdbcTemplate.queryForObject("SELECT b.id from " + Tables.SysManageUser + " a LEFT JOIN " + Tables.SysManageGroup + " b ON a.group_id=b.id where a.id=? ", Integer.class, userId);
        if (groupId == 1) return true;

        /**2.数据库未添加菜单默认已授权**/
        var urlList = jdbcTemplate.query("select trim(wmp.url) url from " + Tables.SysManagePermission + " wmp", new ResultSetExtractor<List<String>>() {
            @Override
            public List<String> extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                List<String> urlList = new ArrayList<>();
                while (resultSet.next()) {
                    urlList.add(resultSet.getString("url"));
                }
                return urlList;
            }
        });
        if (urlList.contains(url) == false) return true;

        /**3.检查访问url是否已授权**/
        String permissionkeySQL = "select trim(wmp.url) url from " + Tables.SysManageUserPermission + " wmup LEFT JOIN " + Tables.SysManagePermission + " wmp ON wmup.permission_id=wmp.id where wmup.user_id=? and wmup.status=0 ";
        var list = jdbcTemplate.query(permissionkeySQL, new ResultSetExtractor<List<String>>() {
            @Override
            public List<String> extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                List<String> urlList = new ArrayList<>();
                while (resultSet.next()) {
                    urlList.add(resultSet.getString("url"));
                }
                return urlList;
            }
        }, userId);
        return list.contains(url.strip());
    }

    public WmsManagePermissionEntity getEntityByKey(String permissionKey) {
        String sql = "SELECT id,`name`,url,permission_key,is_menu,sort,(SELECT p.permission_key from  " + Tables.SysManagePermission + " as p where p.id=m.parent_id ) as parentKey" +
                ",m.parent_id,system_id,`status`,icon,add_time FROM " + Tables.SysManagePermission + " as m WHERE permission_key=? LIMIT 1";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(WmsManagePermissionEntity.class), permissionKey);
    }

    /**
     * 初始化用户权限菜单
     *
     * @param userId
     */
    public void initUserPermissionMenu(Integer userId) {
        var mUser = getWmsManageUser(userId);
        var list = jdbcTemplate.query("select * from " + Tables.SysManagePermission + " where status=0", new BeanPropertyRowMapper<>(WmsManagePermissionEntity.class));
        list.forEach(menu -> {
            try {
                jdbcTemplate.queryForObject("select id from " + Tables.SysManageUserPermission + " where user_id=? and permission_id=? ", Integer.class, userId, menu.getId());
            } catch (Exception e) {
                try {
                    jdbcTemplate.update("insert into " + Tables.SysManageUserPermission + " set status=?,user_id=?,permission_id=?", mUser.getGroupId() == 1 ? 0 : 1, userId, menu.getId());
                } catch (Exception e1) {
                    throw e1;
                }

            }
        });
    }

    /**
     * 查询用户权限菜单列表
     *
     * @param userId
     * @param type   0 所有菜单 1授权菜单
     * @return
     */
    public List<WmsManageUserMenuVo> getUserPerMissionMenuByWMS(Integer userId, Integer type) {
        StringBuilder sb = new StringBuilder("select wmp.id,wmp.name,wmp.url,wmp.permission_key,wmp.is_menu,wmp.parent_id,wmup.status,wmp.icon from " + Tables.SysManagePermission + " wmp " +
                "LEFT JOIN " + Tables.SysManageUserPermission + " wmup ON wmp.id=wmup.permission_id " +
                "where wmp.status=0 AND wmup.user_id=? AND system_id='WMS'  AND wmp.parent_id=?");
        if (type == 1) sb.append(" and wmup.status=0");
        sb.append(" order by wmp.sort desc,wmp.id asc");

        var list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(WmsManageUserMenuVo.class), userId, 0);
        list.forEach(menu -> {
            menu.setChirdMenus(jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(WmsManageUserMenuVo.class), userId, menu.getId()));
        });
        return list;
    }

    /**
     * 查询用户权限菜单列表
     *
     * @param userId
     * @param type   0 所有菜单 1授权菜单
     * @return
     */
    public List<WmsManageUserMenuVo> getUserPerMissionMenuByFMS(Integer userId, Integer type) {
        StringBuilder sb = new StringBuilder("select wmp.id,wmp.name,wmp.url,wmp.permission_key,wmp.is_menu,wmp.parent_id,wmup.status from wms_manage_permission wmp " +
                "LEFT JOIN wms_manage_user_permission wmup ON wmp.id=wmup.permission_id " +
                "where wmp.status=0 AND wmup.user_id=? AND system_id='FMS'  AND wmp.parent_id=?");
        if (type == 1) sb.append(" and wmup.status=0");
        sb.append(" order by wmp.sort desc,wmp.id asc");

        var list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(WmsManageUserMenuVo.class), userId, 0);
        list.forEach(menu -> {
            menu.setChirdMenus(jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(WmsManageUserMenuVo.class), userId, menu.getId()));
        });
        return list;
    }

    /**
     * 设置用户权限菜单
     *
     * @param userId  用户id
     * @param menuIds 菜单列表
     */
    @Transactional
    public void setManageUserMenu(Integer userId, String[] menuIds) {


//        /*******1、删除现有权限********/
//        jdbcTemplate.update("delete from wms_manage_user_permission where user_id=?",userId);
//
//
//        /*******2、添加新权限********/
//        for (var menuId:menuIds) {
//            try {
//                //查询菜单
//                var menu = jdbcTemplate.queryForObject("select * from wms_manage_permission where id=?", new BeanPropertyRowMapper<>(WmsManagePermissionEntity.class), Integer.parseInt(menuId));
//
//                //添加自己
//                jdbcTemplate.update("update wms_manage_user_permission SET status=0 WHERE user_id=? and permission_id=?", userId, Integer.parseInt(menuId));
//
//                //查询上级是否存在
//                try {
//                    jdbcTemplate.queryForObject("SELECT * FROM wms_manage_user_permission WHERE user_id=? and permission_id=?", new BeanPropertyRowMapper<>(WmsManageUserPermissionEntity.class), menu.getParentId());
//                    //上级存在，不操作
//                }catch (Exception e1){
//                    //上级不存在，添加
//                    jdbcTemplate.update("update wms_manage_user_permission SET status=0 WHERE user_id=? and permission_id=?", userId, menu.getParentId());
//                }
//            }catch (Exception e){
//                //不存在，不添加菜单
//            }
//        }
//

        /**1.未选择任何菜单全部禁用**/


        if (StringUtils.isEmpty(menuIds)) {
            jdbcTemplate.update("update wms_manage_user_permission SET status=1 WHERE user_id=? and ", userId);
            return;
        }
        List<String> newMenus = Arrays.asList(menuIds);

        /**2.取消菜单权限**/
        var list = jdbcTemplate.query("select permission_id from "+Tables.SysManageUserPermission+"  where user_id=?", new BeanPropertyRowMapper<>(WmsMangeUserPermissionKeyVo.class), userId);

        list.forEach(oldMenu -> {
            if (newMenus.contains(oldMenu.getPermissionId()) == false) {
                jdbcTemplate.update("UPDATE "+Tables.SysManageUserPermission+" wmup LEFT JOIN "+Tables.SysManagePermission+" wmp ON wmup.permission_id=wmp.id SET wmup.status=1 WHERE wmp.parent_id>0 and wmup.user_id=? and wmup.permission_id=? ", userId, oldMenu.getPermissionId());
            }
        });


        /**3.设置用户最新菜单权限**/
        newMenus.forEach(menus -> {
            jdbcTemplate.update("update "+Tables.SysManageUserPermission+" SET status=0 WHERE user_id=? and permission_id=?", userId, Integer.parseInt(menus));
        });

        /***3.1把新菜单上级权限改回来*****/
        //查询出一级菜单
        //var parentList = jdbcTemplate.query("SELECT * FROM wms_manage_permission where parent_id=0 AND status =0 ",new BeanPropertyRowMapper<>(WmsManagePermissionEntity.class));

        for (var mId : menuIds) {
            try {
                var menu = jdbcTemplate.queryForObject("SELECT * FROM "+Tables.SysManagePermission+" where id=?", new BeanPropertyRowMapper<>(WmsManagePermissionEntity.class), Integer.parseInt(mId));
                jdbcTemplate.update("update "+Tables.SysManageUserPermission+" SET status=0 WHERE permission_id=? and user_id=?", menu.getParentId(), userId);
            } catch (Exception e) {
                throw e;
            }
        }

    }

    /**
     * 修改用户登录密码
     *
     * @param newPwd
     * @return
     */
    public void updWmsUserPwd(int userId, String newPwd, String pwd_rand) {
        jdbcTemplate.update("UPDATE "+Tables.SysManageUser+" SET user_pwd = ?,user_salf=? WHERE id = ? ", newPwd, pwd_rand, userId);
    }
}
